﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using GlxRRSDB;
using GlxRRSDB.ReportDataSetTableAdapters;
using GlxRRSDB.Data;
using System.Data.SqlClient;
using System.Data;
using GlxRRSDB.Utils;

namespace GlxRRSDB.Facades
{
    public class DailyReportFacade
    {
        //private static Dictionary<int, int> dictTotalSeat = null;
        private static Dictionary<int, List<int>> dictSeatByCinema = null;
        private static SqlConnection _connection = null;

        //private static Dictionary<int, int> TotalSeat
        //{
        //    get
        //    {
        //        if (dictTotalSeat == null)
        //        {
        //            dictTotalSeat = getTotalSeat();
        //        }
        //        return dictTotalSeat;
        //    }
        //}

        private static Dictionary<int, List<int>> SeatByCinema
        {
            get
            {
                if (dictSeatByCinema == null)
                {
                    dictSeatByCinema = getSeatByCinema();
                }
                return dictSeatByCinema;
            }
        }

        private static List<int> getSeatByCinema(int cinemaId)
        {
            List<int> dict = new List<int>();
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_SEAT_BY_CINEMA);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.Add(new SqlParameter("@cinemaid", SqlDbType.Int));
            //
            cmd.Parameters[0].Value = cinemaId;
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int screen = reader.GetInt32(0);
                    dict.Add(screen);
                    screen = reader.GetInt32(1);
                    dict.Add(screen);
                    screen = reader.GetInt32(2);
                    dict.Add(screen);
                    screen = reader.GetInt32(3);
                    dict.Add(screen);
                    screen = reader.GetInt32(4);
                    dict.Add(screen);
                }
            }
            reader.Close();

            return dict;
        }

        private static Dictionary<int, List<int>> getSeatByCinema()
        {
            Dictionary<int, List<int>> dict = new Dictionary<int, List<int>>();
            dict.Add(1001, getSeatByCinema(1001));
            dict.Add(1002, getSeatByCinema(1002));
            dict.Add(1003, getSeatByCinema(1003));
            return dict;
        }

        private static Dictionary<int, int> getTotalSeat()
        {
            Dictionary<int, int> dict = new Dictionary<int, int>();
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_TOTAL_SEAT);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int cinemaID = reader.GetInt32(0);
                    int total = reader.GetInt32(1);
                    dict.Add(cinemaID, total);
                }
            }
            reader.Close();

            return dict;
        }
        private static SqlConnection Connection
        {
            get
            {
                if (_connection == null)
                {
                    _connection = new SqlConnection();
                    _connection.ConnectionString = GlxRRSDB.Properties.Settings.Default.ReportDBConnectionString;
                }
                return _connection;
            }
        }
        //private const string SQL_ADMISSION = "SELECT CinemaID, SUM(Admission) AS TotalAdmission " +
        //                            "FROM tblFilmRevenue " +
        //                            "WHERE (DATEDIFF(day, (@fromdate), DATE) >= 0) AND (DATEDIFF(day, (@todate), DATE) <= 0)" +
        //                            "GROUP BY CinemaID";
        private const string SQL_ADMISSION_EX = "SELECT CinemaID, SUM(Admission) AS TotalAdmission " +
                                    "FROM tblBORevenue " +
                                    "WHERE (DATEDIFF(day, (@fromdate), DATE) >= 0) AND (DATEDIFF(day, (@todate), DATE) <= 0)" +
                                    "GROUP BY CinemaID";
        private const string SQL_BO = "SELECT CinemaID, SUM(BO) AS TotalBO " +
                                    "FROM tblBORevenue " +
                                    "WHERE (DATEDIFF(day, (@fromdate), DATE) >= 0) AND (DATEDIFF(day, (@todate), DATE) <= 0)" +
                                    "GROUP BY CinemaID";
        private const string SQL_CO = "SELECT CinemaID, SUM(CO) AS TotalCO " +
                                    "FROM tblCORevenue " +
                                    "WHERE (DATEDIFF(day, (@fromdate), DATE) >= 0) AND (DATEDIFF(day, (@todate), DATE) <= 0)" +
                                    "GROUP BY CinemaID";
        private const string SQL_OCCUPANCY = "SELECT CinemaID, COUNT(CinemaID), SUM(Screen1+Screen2+Screen3+Screen4+Screen5) " +
                                    "FROM tblSession " +
                                    "WHERE (DATEDIFF(day, (@fromdate), DATE) >= 0) AND (DATEDIFF(day, (@todate), DATE) <= 0)" +
                                    "GROUP BY CinemaID";
        private const string SQL_OCCUPANCY_BY_CINEMA = "SELECT Screen1,Screen2,Screen3,Screen4,Screen5 " +
                                    "FROM tblSession " +
                                    "WHERE CinemaID=@cinemaID AND (DATEDIFF(day, (@fromdate), DATE) >= 0) AND (DATEDIFF(day, (@todate), DATE) <= 0)";
        private const string SQL_TOTAL_SEAT = "SELECT ID, SUM(Screen1+Screen2+Screen3+Screen4+Screen5) " +
                                    "FROM tblCinema GROUP BY ID";
        private const string SQL_SEAT_BY_CINEMA = "SELECT Screen1,Screen2,Screen3,Screen4,Screen5 " +
                                    "FROM tblCinema WHERE ID=@cinemaid";
        //private const string SQL_DETAIL_ADMISSION = "SELECT CinemaID, Admission, Date " +
        //                           "FROM tblFilmRevenue " +
        //                           "WHERE (DATEDIFF(day, (@fromdate), DATE) >= 0) AND (DATEDIFF(day, (@todate), DATE) <= 0)" +
        //                           "ORDER BY CINEMAID ASC, DATE ASC";
        private const string SQL_DETAIL_ADMISSION_EX = "SELECT CinemaID, Admission, Date " +
                                   "FROM tblBORevenue " +
                                   "WHERE (DATEDIFF(day, (@fromdate), DATE) >= 0) AND (DATEDIFF(day, (@todate), DATE) <= 0)" +
                                   "ORDER BY CINEMAID ASC, DATE ASC";
        private const string SQL_DETAIL_BO = "SELECT CinemaID, BO, Date " +
                                   "FROM tblBORevenue " +
                                   "WHERE (DATEDIFF(day, (@fromdate), DATE) >= 0) AND (DATEDIFF(day, (@todate), DATE) <= 0)" +
                                   "ORDER BY CINEMAID ASC, DATE ASC";
        private const string SQL_DETAIL_CO = "SELECT CinemaID, CO, Date " +
                                   "FROM tblCORevenue " +
                                   "WHERE (DATEDIFF(day, (@fromdate), DATE) >= 0) AND (DATEDIFF(day, (@todate), DATE) <= 0)" +
                                   "ORDER BY CINEMAID ASC, DATE ASC";
        private const string SQL_DETAIL_FILM_ADMISSION = "SELECT FilmID, CinemaID, Admission, Date, FilmTitle " +
                                   "FROM FilmFilmRevenueView " +
                                   "WHERE (DATEDIFF(day, (@fromdate), DATE) >= 0) AND (DATEDIFF(day, (@todate), DATE) <= 0)" +
                                   "ORDER BY FilmID ASC, DATE ASC";
        private const string SQL_DETAIL_FILM_BO = "SELECT FilmID, CinemaID, BO, Date, FilmTitle " +
                                   "FROM FilmFilmRevenueView " +
                                   "WHERE (DATEDIFF(day, (@fromdate), DATE) >= 0) AND (DATEDIFF(day, (@todate), DATE) <= 0)" +
                                   "ORDER BY FilmID ASC, DATE ASC";

        public static Dictionary<int, DailyReportData> getTotalAdmissionEx(DateTime fromdate, DateTime todate, Dictionary<int, DailyReportData> dict)
        {
            getCurrentDateAdmissionEx(fromdate, dict);
            getCurrentWeekAdmissionEx(fromdate, dict);
            getCurrentMonthAdmissionEx(fromdate, dict);
            getCurrentQuarterAdmissionEx(fromdate, dict);
            getCurrentYearAdmissionEx(fromdate, dict);
            //

            return dict;
        }
        public static Dictionary<int, DailyReportData> getTotalBOEx(DateTime fromdate, DateTime todate, Dictionary<int, DailyReportData> dict)
        {
            getCurrentDateBOEx(fromdate, dict);
            getCurrentWeekBOEx(fromdate, dict);
            getCurrentMonthBOEx(fromdate, dict);
            getCurrentQuarterBOEx(fromdate, dict);
            getCurrentYearBOEx(fromdate, dict);
            //

            return dict;
        }
        public static Dictionary<int, DailyReportData> getTotalCOEx(DateTime fromdate, DateTime todate, Dictionary<int, DailyReportData> dict)
        {
            getCurrentDateCOEx(fromdate, dict);
            getCurrentWeekCOEx(fromdate, dict);
            getCurrentMonthCOEx(fromdate, dict);
            getCurrentQuarterCOEx(fromdate, dict);
            getCurrentYearCOEx(fromdate, dict);
            //

            return dict;
        }
        public static Dictionary<int, DailyReportData> getTotalOccupancyEx(DateTime fromdate, DateTime todate, Dictionary<int, DailyReportData> dict)
        {
            getCurrentDateOccupancyEx(fromdate, dict);

            return dict;
        }
        #region ADMISSION
        public static Dictionary<int, DailyReportData> getCurrentDateAdmissionEx(DateTime fromdate, Dictionary<int, DailyReportData> dict)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_ADMISSION_EX);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = fromdate;
            cmd.Parameters[1].Value = fromdate;
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int cinemaID = reader.GetInt32(0);
                    //
                    if (dict.ContainsKey(cinemaID))
                    {
                        DailyReportData data = dict[cinemaID];
                        data.currentDate = (double)reader.GetInt32(1);
                        //dict.Add(data.CinemaID, data);
                    }
                    else
                    {
                        DailyReportData data = new DailyReportData();
                        data.CinemaID = cinemaID;
                        data.currentDate = (double)reader.GetInt32(1);
                        dict.Add(data.CinemaID, data);
                    }
                }
            }
            reader.Close();

            return dict;
        }
        public static Dictionary<int, DailyReportData> getCurrentWeekAdmissionEx(DateTime fromdate, Dictionary<int, DailyReportData> dict)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_ADMISSION_EX);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = fromdate.StartOfWeek(DayOfWeek.Friday);
            cmd.Parameters[1].Value = fromdate.EndOfWeek(DayOfWeek.Friday);
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int cinemaID = reader.GetInt32(0);
                    //
                    if (dict.ContainsKey(cinemaID))
                    {
                        DailyReportData data = dict[cinemaID];
                        data.currentWeek = (double)reader.GetInt32(1);
                        //dict.Add(data.CinemaID, data);
                    }
                    else
                    {
                        DailyReportData data = new DailyReportData();
                        data.CinemaID = cinemaID;
                        data.currentWeek = (double)reader.GetInt32(1);
                        dict.Add(data.CinemaID, data);
                    }
                }
            }
            reader.Close();

            return dict;
        }
        public static Dictionary<int, DailyReportData> getCurrentMonthAdmissionEx(DateTime fromdate, Dictionary<int, DailyReportData> dict)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_ADMISSION_EX);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = fromdate.StartOfMonth();
            cmd.Parameters[1].Value = fromdate.EndOfMonth();
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int cinemaID = reader.GetInt32(0);
                    //
                    if (dict.ContainsKey(cinemaID))
                    {
                        DailyReportData data = dict[cinemaID];
                        data.currentMonth = (double)reader.GetInt32(1);
                        //dict.Add(data.CinemaID, data);
                    }
                    else
                    {
                        DailyReportData data = new DailyReportData();
                        data.CinemaID = cinemaID;
                        data.currentMonth = (double)reader.GetInt32(1);
                        dict.Add(data.CinemaID, data);
                    }
                }
            }
            reader.Close();

            return dict;
        }
        public static Dictionary<int, DailyReportData> getCurrentQuarterAdmissionEx(DateTime fromdate, Dictionary<int, DailyReportData> dict)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_ADMISSION_EX);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = fromdate.StartOfQuarter();
            cmd.Parameters[1].Value = fromdate.EndOfQuarter();
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int cinemaID = reader.GetInt32(0);
                    //
                    if (dict.ContainsKey(cinemaID))
                    {
                        DailyReportData data = dict[cinemaID];
                        data.currentQuarter = (double)reader.GetInt32(1);
                        //dict.Add(data.CinemaID, data);
                    }
                    else
                    {
                        DailyReportData data = new DailyReportData();
                        data.CinemaID = cinemaID;
                        data.currentQuarter = (double)reader.GetInt32(1);
                        dict.Add(data.CinemaID, data);
                    }
                }
            }
            reader.Close();

            return dict;
        }
        public static Dictionary<int, DailyReportData> getCurrentYearAdmissionEx(DateTime fromdate, Dictionary<int, DailyReportData> dict)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_ADMISSION_EX);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = fromdate.StartOfYear();
            cmd.Parameters[1].Value = fromdate.EndOfYear();
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int cinemaID = reader.GetInt32(0);
                    //
                    if (dict.ContainsKey(cinemaID))
                    {
                        DailyReportData data = dict[cinemaID];
                        data.currentYear = (double)reader.GetInt32(1);
                        //dict.Add(data.CinemaID, data);
                    }
                    else
                    {
                        DailyReportData data = new DailyReportData();
                        data.CinemaID = cinemaID;
                        data.currentYear = (double)reader.GetInt32(1);
                        dict.Add(data.CinemaID, data);
                    }
                }
            }
            reader.Close();

            return dict;
        }
        #endregion
        #region BO
        public static Dictionary<int, DailyReportData> getCurrentDateBOEx(DateTime fromdate, Dictionary<int, DailyReportData> dict)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_BO);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = fromdate;
            cmd.Parameters[1].Value = fromdate;
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int cinemaID = reader.GetInt32(0);
                    //
                    if (dict.ContainsKey(cinemaID))
                    {
                        DailyReportData data = dict[cinemaID];
                        data.currentDate = reader.GetInt64(1);
                        //dict.Add(data.CinemaID, data);
                    }
                    else
                    {
                        DailyReportData data = new DailyReportData();
                        data.CinemaID = cinemaID;
                        data.currentDate = reader.GetInt64(1);
                        dict.Add(data.CinemaID, data);
                    }
                }
            }
            reader.Close();

            return dict;
        }
        public static Dictionary<int, DailyReportData> getCurrentWeekBOEx(DateTime fromdate, Dictionary<int, DailyReportData> dict)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_BO);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = fromdate.StartOfWeek(DayOfWeek.Friday);
            cmd.Parameters[1].Value = fromdate.EndOfWeek(DayOfWeek.Friday);
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int cinemaID = reader.GetInt32(0);
                    //
                    if (dict.ContainsKey(cinemaID))
                    {
                        DailyReportData data = dict[cinemaID];
                        data.currentWeek = reader.GetInt64(1);
                        //dict.Add(data.CinemaID, data);
                    }
                    else
                    {
                        DailyReportData data = new DailyReportData();
                        data.CinemaID = cinemaID;
                        data.currentWeek = reader.GetInt64(1);
                        dict.Add(data.CinemaID, data);
                    }
                }
            }
            reader.Close();

            return dict;
        }
        public static Dictionary<int, DailyReportData> getCurrentMonthBOEx(DateTime fromdate, Dictionary<int, DailyReportData> dict)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_BO);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = fromdate.StartOfMonth();
            cmd.Parameters[1].Value = fromdate.EndOfMonth();
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int cinemaID = reader.GetInt32(0);
                    //
                    if (dict.ContainsKey(cinemaID))
                    {
                        DailyReportData data = dict[cinemaID];
                        data.currentMonth = reader.GetInt64(1);
                        //dict.Add(data.CinemaID, data);
                    }
                    else
                    {
                        DailyReportData data = new DailyReportData();
                        data.CinemaID = cinemaID;
                        data.currentMonth = (double)reader.GetInt64(1);
                        dict.Add(data.CinemaID, data);
                    }
                }
            }
            reader.Close();

            return dict;
        }
        public static Dictionary<int, DailyReportData> getCurrentQuarterBOEx(DateTime fromdate, Dictionary<int, DailyReportData> dict)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_BO);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = fromdate.StartOfQuarter();
            cmd.Parameters[1].Value = fromdate.EndOfQuarter();
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int cinemaID = reader.GetInt32(0);
                    //
                    if (dict.ContainsKey(cinemaID))
                    {
                        DailyReportData data = dict[cinemaID];
                        data.currentQuarter = reader.GetInt64(1);
                        //dict.Add(data.CinemaID, data);
                    }
                    else
                    {
                        DailyReportData data = new DailyReportData();
                        data.CinemaID = cinemaID;
                        data.currentQuarter = reader.GetInt64(1);
                        dict.Add(data.CinemaID, data);
                    }
                }
            }
            reader.Close();

            return dict;
        }
        public static Dictionary<int, DailyReportData> getCurrentYearBOEx(DateTime fromdate, Dictionary<int, DailyReportData> dict)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_BO);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = fromdate.StartOfYear();
            cmd.Parameters[1].Value = fromdate.EndOfYear();
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int cinemaID = reader.GetInt32(0);
                    //
                    if (dict.ContainsKey(cinemaID))
                    {
                        DailyReportData data = dict[cinemaID];
                        data.currentYear = reader.GetInt64(1);
                        //dict.Add(data.CinemaID, data);
                    }
                    else
                    {
                        DailyReportData data = new DailyReportData();
                        data.CinemaID = cinemaID;
                        data.currentYear = reader.GetInt64(1);
                        dict.Add(data.CinemaID, data);
                    }
                }
            }
            reader.Close();

            return dict;
        }
        #endregion
        #region CO
        public static Dictionary<int, DailyReportData> getCurrentDateCOEx(DateTime fromdate, Dictionary<int, DailyReportData> dict)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_CO);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = fromdate;
            cmd.Parameters[1].Value = fromdate;
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int cinemaID = reader.GetInt32(0);
                    //
                    if (dict.ContainsKey(cinemaID))
                    {
                        DailyReportData data = dict[cinemaID];
                        data.currentDate = reader.GetInt64(1);
                        //dict.Add(data.CinemaID, data);
                    }
                    else
                    {
                        DailyReportData data = new DailyReportData();
                        data.CinemaID = cinemaID;
                        data.currentDate = reader.GetInt64(1);
                        dict.Add(data.CinemaID, data);
                    }
                }
            }
            reader.Close();

            return dict;
        }
        public static Dictionary<int, DailyReportData> getCurrentWeekCOEx(DateTime fromdate, Dictionary<int, DailyReportData> dict)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_CO);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = fromdate.StartOfWeek(DayOfWeek.Friday);
            cmd.Parameters[1].Value = fromdate.EndOfWeek(DayOfWeek.Friday);
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int cinemaID = reader.GetInt32(0);
                    //
                    if (dict.ContainsKey(cinemaID))
                    {
                        DailyReportData data = dict[cinemaID];
                        data.currentWeek = reader.GetInt64(1);
                        //dict.Add(data.CinemaID, data);
                    }
                    else
                    {
                        DailyReportData data = new DailyReportData();
                        data.CinemaID = cinemaID;
                        data.currentWeek = reader.GetInt64(1);
                        dict.Add(data.CinemaID, data);
                    }
                }
            }
            reader.Close();

            return dict;
        }
        public static Dictionary<int, DailyReportData> getCurrentMonthCOEx(DateTime fromdate, Dictionary<int, DailyReportData> dict)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_CO);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = fromdate.StartOfMonth();
            cmd.Parameters[1].Value = fromdate.EndOfMonth();
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int cinemaID = reader.GetInt32(0);
                    //
                    if (dict.ContainsKey(cinemaID))
                    {
                        DailyReportData data = dict[cinemaID];
                        data.currentMonth = reader.GetInt64(1);
                        //dict.Add(data.CinemaID, data);
                    }
                    else
                    {
                        DailyReportData data = new DailyReportData();
                        data.CinemaID = cinemaID;
                        data.currentMonth = (double)reader.GetInt64(1);
                        dict.Add(data.CinemaID, data);
                    }
                }
            }
            reader.Close();

            return dict;
        }
        public static Dictionary<int, DailyReportData> getCurrentQuarterCOEx(DateTime fromdate, Dictionary<int, DailyReportData> dict)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_CO);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = fromdate.StartOfQuarter();
            cmd.Parameters[1].Value = fromdate.EndOfQuarter();
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int cinemaID = reader.GetInt32(0);
                    //
                    if (dict.ContainsKey(cinemaID))
                    {
                        DailyReportData data = dict[cinemaID];
                        data.currentQuarter = reader.GetInt64(1);
                        //dict.Add(data.CinemaID, data);
                    }
                    else
                    {
                        DailyReportData data = new DailyReportData();
                        data.CinemaID = cinemaID;
                        data.currentQuarter = reader.GetInt64(1);
                        dict.Add(data.CinemaID, data);
                    }
                }
            }
            reader.Close();

            return dict;
        }
        public static Dictionary<int, DailyReportData> getCurrentYearCOEx(DateTime fromdate, Dictionary<int, DailyReportData> dict)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_CO);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = fromdate.StartOfYear();
            cmd.Parameters[1].Value = fromdate.EndOfYear();
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int cinemaID = reader.GetInt32(0);
                    //
                    if (dict.ContainsKey(cinemaID))
                    {
                        DailyReportData data = dict[cinemaID];
                        data.currentYear = reader.GetInt64(1);
                        //dict.Add(data.CinemaID, data);
                    }
                    else
                    {
                        DailyReportData data = new DailyReportData();
                        data.CinemaID = cinemaID;
                        data.currentYear = reader.GetInt64(1);
                        dict.Add(data.CinemaID, data);
                    }
                }
            }
            reader.Close();

            return dict;
        }
        #endregion
        #region OCCUPANCY
        public static Dictionary<int, DailyReportData> getCurrentDateOccupancyEx(DateTime fromdate, Dictionary<int, DailyReportData> dict)
        {
            DailyReportData data1001 = new DailyReportData();
            DailyReportData data1002 = new DailyReportData();
            DailyReportData data1003 = new DailyReportData();
            //
            data1001.currentDate = getOccupancyByCinema(1001, fromdate, fromdate);
            data1002.currentDate = getOccupancyByCinema(1002, fromdate, fromdate);
            data1003.currentDate = getOccupancyByCinema(1003, fromdate, fromdate);
            //
            data1001.currentWeek = getOccupancyByCinema(1001, fromdate.StartOfWeek(DayOfWeek.Friday), fromdate.EndOfWeek(DayOfWeek.Friday));
            data1002.currentWeek = getOccupancyByCinema(1002, fromdate.StartOfWeek(DayOfWeek.Friday), fromdate.EndOfWeek(DayOfWeek.Friday));
            data1003.currentWeek = getOccupancyByCinema(1003, fromdate.StartOfWeek(DayOfWeek.Friday), fromdate.EndOfWeek(DayOfWeek.Friday));
            //
            data1001.currentMonth = getOccupancyByCinema(1001, fromdate.StartOfMonth(), fromdate.EndOfMonth());
            data1002.currentMonth = getOccupancyByCinema(1002, fromdate.StartOfMonth(), fromdate.EndOfMonth());
            data1003.currentMonth = getOccupancyByCinema(1003, fromdate.StartOfMonth(), fromdate.EndOfMonth());
            //
            data1001.currentQuarter = getOccupancyByCinema(1001, fromdate.StartOfQuarter(), fromdate.EndOfQuarter());
            data1002.currentQuarter = getOccupancyByCinema(1002, fromdate.StartOfQuarter(), fromdate.EndOfQuarter());
            data1003.currentQuarter = getOccupancyByCinema(1003, fromdate.StartOfQuarter(), fromdate.EndOfQuarter());
            //
            data1001.currentYear = getOccupancyByCinema(1001, fromdate.StartOfYear(), fromdate.EndOfYear());
            data1002.currentYear = getOccupancyByCinema(1002, fromdate.StartOfYear(), fromdate.EndOfYear());
            data1003.currentYear = getOccupancyByCinema(1003, fromdate.StartOfYear(), fromdate.EndOfYear());
            //
            dict.Add(1001, data1001);
            dict.Add(1002, data1002);
            dict.Add(1003, data1003);
            return dict;
        }
        //public static DailyReportData getCurrentDateOccupancyByCinema(int cinemaID, DateTime fromdate)
        //{
        //    float totalSeatInSession = 0;
        //    int totalSeatInCinema = 0;
        //    List<int> listSeatInCinema = SeatByCinema[cinemaID];
        //    //
        //    SqlDataReader reader;
        //    SqlCommand cmd = new SqlCommand(SQL_OCCUPANCY_BY_CINEMA);
        //    cmd.Connection = Connection;
        //    cmd.CommandType = CommandType.Text;
        //    cmd.Parameters.Add(new SqlParameter("@cinemaid", SqlDbType.Int));
        //    cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
        //    cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
        //    //
        //    cmd.Parameters[0].Value = cinemaID;
        //    cmd.Parameters[1].Value = fromdate;
        //    cmd.Parameters[2].Value = fromdate;
        //    if (cmd.Connection.State == ConnectionState.Closed)
        //    {
        //        cmd.Connection.Open();
        //    }
        //    reader = cmd.ExecuteReader();
        //    if (reader.HasRows)
        //    {
        //        while (reader.Read())
        //        {
        //            int screen1 = reader.GetInt32(0);
        //            if (screen1 > 0)
        //            {
        //                totalSeatInSession += screen1;
        //                totalSeatInCinema += listSeatInCinema[0];
        //            }
        //            int screen2 = reader.GetInt32(1);
        //            if (screen2 > 0)
        //            {
        //                totalSeatInSession += screen2;
        //                totalSeatInCinema += listSeatInCinema[1];
        //            }
        //            int screen3 = reader.GetInt32(2);
        //            if (screen3 > 0)
        //            {
        //                totalSeatInSession += screen3;
        //                totalSeatInCinema += listSeatInCinema[2];
        //            }
        //            int screen4 = reader.GetInt32(3);
        //            if (screen4 > 0)
        //            {
        //                totalSeatInSession += screen4;
        //                totalSeatInCinema += listSeatInCinema[3];
        //            }
        //            int screen5 = reader.GetInt32(4);
        //            if (screen5 > 0)
        //            {
        //                totalSeatInSession += screen5;
        //                totalSeatInCinema += listSeatInCinema[4];
        //            }
        //        }
        //    }
        //    reader.Close();
        //    DailyReportData data = new DailyReportData();
        //    data.CinemaID = cinemaID;
        //    data.currentDate = totalSeatInSession / totalSeatInCinema;
        //    return data;
        //}

        public static float getOccupancyByCinema(int cinemaID, DateTime fromdate, DateTime todate)
        {
            float totalSeatInSession = 0;
            int totalSeatInCinema = 0;
            List<int> listSeatInCinema = SeatByCinema[cinemaID];
            //
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_OCCUPANCY_BY_CINEMA);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@cinemaid", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = cinemaID;
            cmd.Parameters[1].Value = fromdate;
            cmd.Parameters[2].Value = todate;
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int screen1 = reader.GetInt32(0);
                    if (screen1 > 0)
                    {
                        totalSeatInSession += screen1;
                        totalSeatInCinema += listSeatInCinema[0];
                    }
                    int screen2 = reader.GetInt32(1);
                    if (screen2 > 0)
                    {
                        totalSeatInSession += screen2;
                        totalSeatInCinema += listSeatInCinema[1];
                    }
                    int screen3 = reader.GetInt32(2);
                    if (screen3 > 0)
                    {
                        totalSeatInSession += screen3;
                        totalSeatInCinema += listSeatInCinema[2];
                    }
                    int screen4 = reader.GetInt32(3);
                    if (screen4 > 0)
                    {
                        totalSeatInSession += screen4;
                        totalSeatInCinema += listSeatInCinema[3];
                    }
                    int screen5 = reader.GetInt32(4);
                    if (screen5 > 0)
                    {
                        totalSeatInSession += screen5;
                        totalSeatInCinema += listSeatInCinema[4];
                    }
                }
            }
            reader.Close();
            return (totalSeatInCinema == 0) ? 0 : totalSeatInSession / totalSeatInCinema;
        }
        public static DailyReportData getCurrentWeekOccupancyByCinema(int cinemaID, DateTime fromdate)
        {
            float totalSeatInSession = 0;
            int totalSeatInCinema = 0;
            List<int> listSeatInCinema = SeatByCinema[cinemaID];
            //
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_OCCUPANCY_BY_CINEMA);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@cinemaid", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = cinemaID;
            cmd.Parameters[1].Value = fromdate.StartOfWeek(DayOfWeek.Friday);
            cmd.Parameters[2].Value = fromdate.EndOfWeek(DayOfWeek.Friday);
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int screen1 = reader.GetInt32(0);
                    if (screen1 > 0)
                    {
                        totalSeatInSession += screen1;
                        totalSeatInCinema += listSeatInCinema[0];
                    }
                    int screen2 = reader.GetInt32(1);
                    if (screen2 > 0)
                    {
                        totalSeatInSession += screen2;
                        totalSeatInCinema += listSeatInCinema[1];
                    }
                    int screen3 = reader.GetInt32(2);
                    if (screen3 > 0)
                    {
                        totalSeatInSession += screen3;
                        totalSeatInCinema += listSeatInCinema[2];
                    }
                    int screen4 = reader.GetInt32(3);
                    if (screen4 > 0)
                    {
                        totalSeatInSession += screen4;
                        totalSeatInCinema += listSeatInCinema[3];
                    }
                    int screen5 = reader.GetInt32(4);
                    if (screen5 > 0)
                    {
                        totalSeatInSession += screen5;
                        totalSeatInCinema += listSeatInCinema[4];
                    }
                }
            }
            reader.Close();
            DailyReportData data = new DailyReportData();
            data.CinemaID = cinemaID;
            data.currentDate = totalSeatInSession / totalSeatInCinema;
            return data;
        }

        public static DailyReportData getCurrentQuarterOccupancyByCinema(int cinemaID, DateTime fromdate)
        {
            float totalSeatInSession = 0;
            int totalSeatInCinema = 0;
            List<int> listSeatInCinema = SeatByCinema[cinemaID];
            //
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_OCCUPANCY_BY_CINEMA);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@cinemaid", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = cinemaID;
            cmd.Parameters[1].Value = fromdate.StartOfQuarter();
            cmd.Parameters[2].Value = fromdate.EndOfQuarter();
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int screen1 = reader.GetInt32(0);
                    if (screen1 > 0)
                    {
                        totalSeatInSession += screen1;
                        totalSeatInCinema += listSeatInCinema[0];
                    }
                    int screen2 = reader.GetInt32(1);
                    if (screen2 > 0)
                    {
                        totalSeatInSession += screen2;
                        totalSeatInCinema += listSeatInCinema[1];
                    }
                    int screen3 = reader.GetInt32(2);
                    if (screen3 > 0)
                    {
                        totalSeatInSession += screen3;
                        totalSeatInCinema += listSeatInCinema[2];
                    }
                    int screen4 = reader.GetInt32(3);
                    if (screen4 > 0)
                    {
                        totalSeatInSession += screen4;
                        totalSeatInCinema += listSeatInCinema[3];
                    }
                    int screen5 = reader.GetInt32(4);
                    if (screen5 > 0)
                    {
                        totalSeatInSession += screen5;
                        totalSeatInCinema += listSeatInCinema[4];
                    }
                }
            }
            reader.Close();
            DailyReportData data = new DailyReportData();
            data.CinemaID = cinemaID;
            data.currentDate = totalSeatInSession / totalSeatInCinema;
            return data;
        }
        #endregion

        public static Dictionary<int, Dictionary<DateTime, DailyReportData>> getDetailAdmissionWeek(DateTime fromdate, Dictionary<int, Dictionary<DateTime, DailyReportData>> dict)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_DETAIL_ADMISSION_EX);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = fromdate.StartOfWeek(DayOfWeek.Friday);
            cmd.Parameters[1].Value = fromdate.EndOfWeek(DayOfWeek.Friday);
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int cinemaID = reader.GetInt32(0);
                    int admission = reader.GetInt32(1);
                    DateTime date = reader.GetDateTime(2);
                    //
                    if (dict.ContainsKey(cinemaID))
                    {
                        Dictionary<DateTime, DailyReportData> dictDateTime = dict[cinemaID];
                        if (dictDateTime.ContainsKey(date.Date))
                        {
                            DailyReportData data = dictDateTime[date.Date];
                            data.currentDate += admission;
                        }
                        else
                        {
                            DailyReportData data = new DailyReportData();
                            data.CinemaID = cinemaID;
                            data.FromDate = date.Date;
                            data.currentDate = admission;
                            dictDateTime.Add(data.FromDate, data);
                        }
                    }
                    else
                    {
                        Dictionary<DateTime, DailyReportData> dictDateTime = new Dictionary<DateTime, DailyReportData>();
                        DailyReportData data = new DailyReportData();
                        data.CinemaID = cinemaID;
                        data.FromDate = date.Date;
                        data.currentDate = admission;
                        dictDateTime.Add(data.FromDate, data);
                        //
                        dict.Add(cinemaID, dictDateTime);
                    }
                }
            }
            reader.Close();

            return dict;
        }
        public static Dictionary<int, Dictionary<DateTime, DailyReportData>> getDetailBOWeek(DateTime fromdate, Dictionary<int, Dictionary<DateTime, DailyReportData>> dict)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_DETAIL_BO);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = fromdate.StartOfWeek(DayOfWeek.Friday);
            cmd.Parameters[1].Value = fromdate.EndOfWeek(DayOfWeek.Friday);
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int cinemaID = reader.GetInt32(0);
                    long admission = reader.GetInt64(1);
                    DateTime date = reader.GetDateTime(2);
                    //
                    if (dict.ContainsKey(cinemaID))
                    {
                        Dictionary<DateTime, DailyReportData> dictDateTime = dict[cinemaID];
                        if (dictDateTime.ContainsKey(date.Date))
                        {
                            DailyReportData data = dictDateTime[date.Date];
                            data.currentDate += admission;
                        }
                        else
                        {
                            DailyReportData data = new DailyReportData();
                            data.CinemaID = cinemaID;
                            data.FromDate = date.Date;
                            data.currentDate = admission;
                            dictDateTime.Add(data.FromDate, data);
                        }
                    }
                    else
                    {
                        Dictionary<DateTime, DailyReportData> dictDateTime = new Dictionary<DateTime, DailyReportData>();
                        DailyReportData data = new DailyReportData();
                        data.CinemaID = cinemaID;
                        data.FromDate = date.Date;
                        data.currentDate = admission;
                        dictDateTime.Add(data.FromDate, data);
                        //
                        dict.Add(cinemaID, dictDateTime);
                    }
                }
            }
            reader.Close();

            return dict;
        }
        public static Dictionary<int, Dictionary<DateTime, DailyReportData>> getDetailCOWeek(DateTime fromdate, Dictionary<int, Dictionary<DateTime, DailyReportData>> dict)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_DETAIL_CO);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = fromdate.StartOfWeek(DayOfWeek.Friday);
            cmd.Parameters[1].Value = fromdate.EndOfWeek(DayOfWeek.Friday);
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int cinemaID = reader.GetInt32(0);
                    long admission = reader.GetInt64(1);
                    DateTime date = reader.GetDateTime(2);
                    //
                    if (dict.ContainsKey(cinemaID))
                    {
                        Dictionary<DateTime, DailyReportData> dictDateTime = dict[cinemaID];
                        if (dictDateTime.ContainsKey(date.Date))
                        {
                            DailyReportData data = dictDateTime[date.Date];
                            data.currentDate += admission;
                        }
                        else
                        {
                            DailyReportData data = new DailyReportData();
                            data.CinemaID = cinemaID;
                            data.FromDate = date.Date;
                            data.currentDate = admission;
                            dictDateTime.Add(data.FromDate, data);
                        }
                    }
                    else
                    {
                        Dictionary<DateTime, DailyReportData> dictDateTime = new Dictionary<DateTime, DailyReportData>();
                        DailyReportData data = new DailyReportData();
                        data.CinemaID = cinemaID;
                        data.FromDate = date.Date;
                        data.currentDate = admission;
                        dictDateTime.Add(data.FromDate, data);
                        //
                        dict.Add(cinemaID, dictDateTime);
                    }
                }
            }
            reader.Close();

            return dict;
        }

        public static Dictionary<int, Dictionary<DateTime, DailyReportData>> getDetailFilmAdmissionWeek(DateTime fromdate, Dictionary<int, Dictionary<DateTime, DailyReportData>> dict)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_DETAIL_FILM_ADMISSION);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = fromdate.StartOfWeek(DayOfWeek.Friday);
            cmd.Parameters[1].Value = fromdate.EndOfWeek(DayOfWeek.Friday);
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int filmId = reader.GetInt32(0);
                    int cinemaId = reader.GetInt32(1);
                    int admission = reader.GetInt32(2);
                    DateTime date = reader.GetDateTime(3);
                    string filmTitle = reader.GetString(4);
                    //
                    if (dict.ContainsKey(filmId))
                    {
                        Dictionary<DateTime, DailyReportData> dictDateTime = dict[filmId];
                        if (dictDateTime.ContainsKey(date.Date))
                        {
                            DailyReportData data = dictDateTime[date.Date];
                            data.currentDate += admission;
                        }
                        else
                        {
                            DailyReportData data = new DailyReportData();
                            data.CinemaID = cinemaId;
                            data.FilmTitle = filmTitle;
                            data.FromDate = date.Date;
                            data.currentDate = admission;
                            dictDateTime.Add(data.FromDate, data);
                        }
                    }
                    else
                    {
                        Dictionary<DateTime, DailyReportData> dictDateTime = new Dictionary<DateTime, DailyReportData>();
                        DailyReportData data = new DailyReportData();
                        data.CinemaID = cinemaId;
                        data.FilmTitle = filmTitle;
                        data.FromDate = date.Date;
                        data.currentDate = admission;
                        dictDateTime.Add(data.FromDate, data);
                        //
                        dict.Add(filmId, dictDateTime);
                    }
                }
            }
            reader.Close();

            return dict;
        }
        public static Dictionary<int, Dictionary<DateTime, DailyReportData>> getDetailFilmBOWeek(DateTime fromdate, Dictionary<int, Dictionary<DateTime, DailyReportData>> dict)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_DETAIL_FILM_BO);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = fromdate.StartOfWeek(DayOfWeek.Friday);
            cmd.Parameters[1].Value = fromdate.EndOfWeek(DayOfWeek.Friday);
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int filmId = reader.GetInt32(0);
                    int cinemaId = reader.GetInt32(1);
                    long admission = reader.GetInt64(2);
                    DateTime date = reader.GetDateTime(3);
                    string filmTitle = reader.GetString(4);
                    //
                    if (dict.ContainsKey(filmId))
                    {
                        Dictionary<DateTime, DailyReportData> dictDateTime = dict[filmId];
                        if (dictDateTime.ContainsKey(date.Date))
                        {
                            DailyReportData data = dictDateTime[date.Date];
                            data.currentDate += admission;
                        }
                        else
                        {
                            DailyReportData data = new DailyReportData();
                            data.CinemaID = cinemaId;
                            data.FilmTitle = filmTitle;
                            data.FromDate = date.Date;
                            data.currentDate = admission;
                            dictDateTime.Add(data.FromDate, data);
                        }
                    }
                    else
                    {
                        Dictionary<DateTime, DailyReportData> dictDateTime = new Dictionary<DateTime, DailyReportData>();
                        DailyReportData data = new DailyReportData();
                        data.CinemaID = cinemaId;
                        data.FilmTitle = filmTitle;
                        data.FromDate = date.Date;
                        data.currentDate = admission;
                        dictDateTime.Add(data.FromDate, data);
                        //
                        dict.Add(filmId, dictDateTime);
                    }
                }
            }
            reader.Close();

            return dict;
        }
    }
}
